<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/**
 * Statistics Model
 *
 * @package     VisualQ Local System
 * @author      CSU OJT Students '10 - '11 (Michael Roland B. Martin)
 * @copyright   Copyright (c) 2010 - 2011, Bitstop Network Services, Inc.
 * @version     1.0 January 27, 2011
 */

class Statistics_model extends Model {

    function Statistics_model() {
        parent::Model();

        $this->CI =& get_instance();

        // Load necessary library, helper and model files
        $this->load->helper('date');
    }

    /**
     * Initialize branch statistics
     *
     * Used by:
     *      manager/front_desk/
     */
    function initialize_stats()
    {
        // Get manager account details
        $this->db->where('account_type', 'manager');
        $query = $this->db->get('accounts');
        $manager = $query->row_array();

        // Check if the branch logs statistics is already initialized
        // Where condition
        $where = array(
            'date'          => mdate('%Y-%m-%d', now()),
            'account_id'    => $manager['id']
        );
        $this->db->where($where);
        $query = $this->db->get('statistics');

        if( ! $query->num_rows() > 0)
        {
            $this->db->insert('statistics', $where);
        }

        // Get all services details
        $query = $this->db->get('services');

        if($query->num_rows() > 0)
        {
            foreach($query->result_array() as $s_row)
            {
                // Check if the service statistics is already initialized
                // Where condition
                $where = array(
                    'date'          => mdate('%Y-%m-%d', now()),
                    'service_id'    => $s_row['id']
                );
                $this->db->where($where);
                $query = $this->db->get('services_tally');

                if( ! $query->num_rows() > 0)
                {
                    $this->db->insert('services_tally', $where);
                }

                foreach(json_decode($s_row['closing_codes']) as $c_row)
                {
                    // Check if the closing codes statistics is already initialized
                    // Where condition
                    $where = array(
                        'date'          => mdate('%Y-%m-%d', now()),
                        'closing_code'  => $c_row,
                        'service_id'    => $s_row['id'],
                        'account_id'    => $manager['id']
                    );
                    $this->db->where($where);
                    $query = $this->db->get('closing_codes_tally');

                    if( ! $query->num_rows() > 0)
                    {
                        $this->db->insert('closing_codes_tally', $where);
                    }
                }
            }
        }
    }

    /**
     * Initialize account statistics
     *
     * Used by:
     *      login/authenticate/
     */
    function initialize_account_statistics()
    {
        // Check if the account's log statistics is initialized
        // Where condition
        $where = array(
            'date' => mdate('%Y-%m-%d', now()),
            'account_id' => $this->session->userdata('id')
        );
        $this->db->where($where);
        $query = $this->db->get('statistics');

        if( ! $query->num_rows() > 0)
        {
            $this->db->insert('statistics', $where);
        }

        // Get the account's services
        $this->db->join('services', 'accounts_services.service_id = services.id');
        $this->db->where('account_id', $this->session->userdata('id'));
        $query = $this->db->get('accounts_services');
        if($query->num_rows() > 0)
        {
            foreach($query->result_array() as $s_row)
            {
                foreach(json_decode($s_row['closing_codes']) as $c_row)
                {
                    // Check if the account's closing codes statistics is initialized
                    // Where condition
                    $where = array(
                        'date'              => mdate('%Y-%m-%d', now()),
                        'closing_code'      => $c_row,
                        'service_id'        => $s_row['id'],
                        'account_id'        => $this->session->userdata('id')
                    );
                    $this->db->where($where);
                    $query = $this->db->get('closing_codes_tally');

                    if( ! $query->num_rows() > 0)
                    {
                        $this->db->insert('closing_codes_tally', $where);
                    }
                }
            }
        }
    }

    /**
     * Increment the service count
     *
     * Used by:
     *      ajax/new_queue_number/
     *
     * @params  int
     *      si      :   service id
     */
    function increment_service($si)
    {
        // Get the service statistics
        $where = array(
            'date'          => mdate('%Y-%m-%d', now()),
            'service_id'    => $si
        );
        $this->db->where($where);
        $query = $this->db->get('services_tally');

        if($query->num_rows() > 0)
        {
            $row = $query->row_array();

            $this->db->where($where);
            $this->db->update('services_tally', array('frequency' => $row['frequency'] + 1));
        }
    }

    /**
     * Increment the served count
     *
     * Used by:
     *      ajax/end_service/
     *
     * @params  int
     *      pt      :   processing time
     */
    function increment_served($pt)
    {
        // Get manager account details
        $query = $this->db->get_where('accounts', array('account_type' => 'manager'));
        $row = $query->row_array();

        // Set the account ids
        $accounts = array(
            $this->session->userdata('id'),
            $row['id']
        );

        foreach($accounts as $id)
        {
            // Get the account's statistics
            // Where condition
            $where = array(
                'date' => mdate('%Y-%m-%d', now()),
                'account_id' => $id
            );
            $this->db->where($where);
            $query = $this->db->get('statistics');

            $row = $query->row_array();

            // Set update
            $update = array(
                'processing_time'   => ($row['processing_time'] + $pt) / ($row['overall_served'] + 1),
                'overall_served'    => $row['overall_served'] + 1
            );
            
            $this->db->where('id', $row['id']);
            $this->db->update('statistics', $update);
        }
    }

    /**
     * Increment the miss count
     *
     * Used by:
     *      ajax/skip_number/
     */
    function increment_missed()
    {
        // Get manager account
        $query = $this->db->get_where('accounts', array('account_type' => 'manager'));
        $row = $query->row_array();

        $accounts = array(
            $this->session->userdata('id'),
            $row['id']
        );

        // Increment missed count of the branch and the agent
        foreach($accounts as $id)
        {
            $query = $this->db->get_where('statistics', array('date' => mdate('%Y-%m-%d', now()), 'account_id' => $id));

            $row = $query->row_array();
            $update = array(
                'overall_missed' => $row['overall_missed'] + 1
            );
            $this->db->where('id', $row['id']);
            $this->db->update('statistics', $update);
        }
    }

    /**
     * Increment the closing code count
     *
     * Used by:
     *      ajax/skip_number/
     *
     * @params  int
     *      si      :   service id
     *      cc      :   closing code
     */
    function increment_closing_code($params)
    {
        // Get manager account
        $query = $this->db->get_where('accounts', array('account_type' => 'manager'));
        $row = $query->row_array();

        $accounts = array(
            $this->session->userdata('id'),
            $row['id']
        );

        // Increment the closing code count of the manager and the agent
        foreach($accounts as $id)
        {
            $query = $this->db->get_where('closing_codes_tally', array('date' => mdate('%Y-%m-%d', now()), 'account_id' => $id, 'service_id' => $params['si'], 'closing_code' => $params['cc']));

            $row = $query->row_array();
            $update = array(
                'frequency' => $row['frequency'] + 1
            );

            $this->db->where('id', $row['id']);
            $this->db->update('closing_codes_tally', $update);
        }
    }

    /**
     * Get all logs by service
     *
     * Used by:
     *      manager/statistics/
     *
     * @param	array
     *      si              :   service id
     *      type            :   log type
     * @return  string
     *      query           :   the query result
     */
    function get_service_statistics($params)
    {
        if(strcmp($params['type'], 'served') == 0)
        {
            $where = array(
                'service_id'                => $params['si'],
                'closing_code IS NOT NULL'  => NULL
            );
        }
        elseif(strcmp($params['type'], 'missed') == 0)
        {
            $where = array(
                'account_id IS NOT NULL'    => NULL,
                'service_id'                => $params['si'],
                'call_counter >'            => 0,
                'closing_code'              => NULL
            );
        }
        else
        {
            $where = array(
                'service_id'    => $params['si'],
                'closing_code'  => NULL
            );
        }
        $this->db->where($where);
        $this->db->where('DATE(FROM_UNIXTIME(issue_time))', mdate('\'%Y-%m-%d\'', now()), FALSE);
        $query = $this->db->get('logs');

        return $query->num_rows();
    }

    /**
     * Get all service logs
     *
     * Used by:
     *      manager/statistics/
     *
     * @param	int
     *      si              :   service id
     * @return  string
     *      query           :   the query result
     */
    function get_service_logs($si)
    {
        $this->db->where('service_id', $si);
        $this->db->where('DATE(FROM_UNIXTIME(issue_time))', mdate('\'%Y-%m-%d\'', now()), FALSE);
        return $this->db->get('logs');
    }

    /**
     * Get the closing code tally
     *
     * Used by:
     *      manager/statistics/
     *
     * @param	array
     *      si              :   service id
     *      closing_code    :   closing code
     * @return  string
     *      query           :   the query result
     */
    function get_closing_code_tally($params)
    {
        $this->db->where('account_type', 'manager');
        $query = $this->db->get('accounts');
        $manager = $query->row_array();

        $where = array(
            'service_id'    => $params['si'],
            'closing_code'  => $params['closing_code'],
            'date'          => mdate('%Y-%m-%d', now())
        );
        $this->db->where($where);
        $this->db->where_not_in('account_id', $manager['id']);

        return $this->db->get('closing_codes_tally');
    }

    /**
     * Get all logs by account
     *
     * Used by:
     *      manager/statistics/
     *
     * @param	array
     *      ai              :   account id
     *      type            :   log type
     * @return  string
     *      query           :   the query result
     */
    function get_agent_logs($params)
    {
        $where = array(
            'account_id' => $params['ai']
        );
        if(strcmp($params['type'], 'missed') == 0)
        {
            $where['call_counter >'] = 0;
        }
        else
        {
            $where['call_counter'] = 0;
            $where['closing_code IS NOT NULL'] = NULL;
        }
        $this->db->where($where);
        $this->db->where('DATE(FROM_UNIXTIME(issue_time))', mdate('\'%Y-%m-%d\'', now()), FALSE);
        
        return $this->db->get('logs');
    }

    /**
     * Get all statistics
     *
     * Used by:
     *      manager/statistics/
     *
     * @return  string
     *      query           :   the query result
     */
    function get_previous_statistics()
    {
        $this->db->select('UNIX_TIMESTAMP(date) as date, account_id, processing_time, overall_served, overall_missed');
        $this->db->where('DATE(FROM_UNIXTIME(date)) <', mdate('\'%Y-%m-%d\'', now()), FALSE);
        return $this->db->get('statistics');
    }

    /**
     * Get all service tally
     *
     * Used by:
     *      manager/statistics/
     *
     * @return  string
     *      query           :   the query result
     */
    function get_previous_services_tally()
    {
        $this->db->select('UNIX_TIMESTAMP(date) as date, service_id, frequency');
        $this->db->where('DATE(FROM_UNIXTIME(date)) <', mdate('\'%Y-%m-%d\'', now()), FALSE);
        return $this->db->get('services_tally');
    }

    /**
     * Get all closing code tally
     *
     * Used by:
     *      manager/statistics/
     *
     * @return  string
     *      query           :   the query result
     */
    function get_previous_closing_codes_tally()
    {
        $this->db->select('UNIX_TIMESTAMP(date) as date, account_id, service_id, closing_code, frequency');
        $this->db->where('DATE(FROM_UNIXTIME(date)) <', mdate('\'%Y-%m-%d\'', now()), FALSE);
        return $this->db->get('closing_codes_tally');
    }

    function get_previous_dtr()
    {
        $this->db->where('DATE(FROM_UNIXTIME(time)) <', mdate('\'%Y-%m-%d\'', now()), FALSE);
        return $this->db->get('dtr');
    }

}

/* End of file statistics_model.php */
/* Location: ./system/application/model/statistics_model.php */